﻿using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Windows.Forms.DataVisualization.Charting;

using Includes;

namespace Arztmodul
{
   /// <summary>
   /// Mysql database implementation of the persistancy layer interface
   /// </summary>
   public class PersistDoc : IPersistDoc
   {
       private MySqlConnection connection;
       private const String myConnectionString = @"SERVER=localhost;
                                                   DATABASE=homecaresystems;
                                                   UID=hcs;
                                                   PASSWORD=hcs_starting1st;";

       /// <summary>
       /// Standart constructor, creates a Persist doc object, and connects to std database
       /// </summary>
       public PersistDoc()
       {
          connection = new MySqlConnection(myConnectionString);
          try {
             connection.Open();
          }
          catch (MySqlException e) {
//             throw e;
          }
       }

       /// <summary>
       /// Tests whether connection to persistance layer is established, or could be established
       /// </summary>
       /// <returns> true if connection has been established, false otherwise </returns>
       public Boolean connect() 
       {
         if(connection.Ping())
            return true;

         else
         {
            try { 
               connection.Open(); 
            }
            catch (MySqlException e) {
//               throw e;
            }
            return connection.Ping();
         }
       }

       /// <summary>
       /// Gets Vitaldata history of patient
       /// </summary>
       /// <param name="lastName"> Last name of patient </param>
       /// <param name="firstName"> First name of patient </param>
       /// <returns> VData set </returns>
       public VData[] getHistory(String firstName, String lastName)
       {
          List<VData> history = new List<VData>();

          

          MySqlCommand command = connection.CreateCommand();
          command.CommandText = @"SELECT v.Bloodpressure, v.Puls, v.Weight, v.Timestamp
                                  FROM Vitaldaten v, Patientendaten p
                                  WHERE 
                                  p.Vorname = '" + firstName + @"' AND 
                                  p.Nachname = '" + lastName + @"' AND
                                  v.ID = p.ID AND
                                  p.Status = 1 
                                  ORDER BY
                                  v.Timestamp ASC;";

          MySqlDataReader reader = command.ExecuteReader();

          while (reader.Read())
          {
             VData tmp = new VData();

             tmp.bloodpressure = reader.GetInt32("Bloodpressure");
             tmp.pulse = reader.GetInt32("Puls");
             tmp.weight = reader.GetInt32("Weight");
             tmp.time = DateTime.ParseExact(reader.GetString("Timestamp"), "yyyy-MM-dd HH:mm:ss", null);

             history.Add(tmp);
          }

          reader.Close();

          return history.ToArray();
       }

       /// <summary>
       /// Gets Vitaldata history of patient
       /// </summary>
       /// <param name="patient"> Patient to check </param>
       /// <returns> VData set </returns>
       public VData[] getHistory(Patient patient) 
       {
         return getHistory(patient.firstName, patient.name);
       }


       /// <summary>
       /// Persists a patientlogic object
       /// </summary>
       /// <param name="patient"> Patient to persist </param>
       /// <returns> true if successfully persisted, false otherwise </returns>
       public Boolean setPatient(Patient patient)
       {
           String gender = "";

           switch(patient.gender)
           {
               case(Gender.Weiblich):
                  gender = "f";
                  break;
               case(Gender.Männlich):
                  gender = "m";
                  break;
//               case(Gender.NONE):
//                  gender = "n";
//                  break;
               default:
                  break;
           }
           
           // If patient does not exist, create new patient
           if (!patientExists(patient))
           {
                // Enter mapping entry
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = "INSERT INTO mapping (ID, TEL) VALUES (NULL, '" + patient.telephoneNumber + "');";
                command.ExecuteNonQuery();

                // Get new ID
                command = connection.CreateCommand();
                command.CommandText = @"SELECT m.ID 
                                        FROM Mapping m 
                                        WHERE m.TEL = '" + patient.telephoneNumber + "';";
                
                patient.id = Convert.ToInt32(command.ExecuteScalar().ToString());

                // Enter Patientendata
                command.CommandText = @"INSERT INTO Patientendaten (ID, Vorname, Nachname, 
                                       Geburtsdatum, Geschlecht, Diagnose, TelKontakt2,
                                       NormBlutdruck, ZABlutdruck, KABlutdruck, 
                                       NormPuls, ZAPuls, KAPuls, 
                                       NormGewicht, ZAGewicht, KAGewicht, 
                                       Status, Uberprufung1, Uberprufung2) 
                                       VALUES (" + patient.id + ", '" + patient.firstName + "', '" + patient.name + @"', 
                                       DATE('" + patient.dateOfBirth.ToString("yyyy-MM-dd") + "'), '" + gender + "', '" + patient.diagnosis + "', '" + patient.secondTelephoneNumber + @"',
                                       '" + patient.normalBloodPressure + "', '" + patient.acceptableBloodPressure + "', '" + patient.criticalBloodPressure + @"', 
                                       '" + patient.normalPulse + "', '" + patient.acceptablePulse + "', '" + patient.criticalPulse + @"', 
                                       '" + patient.normalWeight + "', '" + patient.acceptableWeight + "', '" + patient.criticalWeight + @"', 
                                       '" + Convert.ToInt32(patient.status) + "', TIME('" + patient.checkTime1.ToString("HH:mm:ss") + "'), TIME('" + patient.checkTime2.ToString("HH:mm:ss") + "'));";

                command.ExecuteNonQuery();

                // Enter Steuerdaten
                command.CommandText = @"INSERT INTO Steuerdaten (ID, Informiert, SuccessiveD, KR, KR_Timestamp) 
                                        VALUES ('" + patient.id + "', '1', '0', '0', CURRENT_TIMESTAMP);";

                command.ExecuteNonQuery();                
           }
           // Otherwise update
           else
           {
                // Phone number changed
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = "UPDATE  Mapping SET TEL = '" + patient.telephoneNumber + "'"+
                                     " WHERE ID = " + patient.id + ";";
                
                command.ExecuteNonQuery();

                // Anything else changed
                command.CommandText = @"UPDATE Patientendaten SET 
                                        Vorname = '" + patient.firstName + "', " +
                                       "Nachname = '" + patient.name + "', " +
                                       "Geburtsdatum =  DATE('" + patient.dateOfBirth.ToShortDateString() + "'), " +
                                       "Geschlecht =  '" + gender + "', " +
                                       "Diagnose =  '"+patient.diagnosis+"', " +
                                       "TelKontakt2 =  '"+patient.secondTelephoneNumber+"', " +
                                       "NormBlutdruck =  '"+patient.normalBloodPressure+"', " +
                                       "ZABlutdruck =  '" + patient.acceptableBloodPressure + "', " +
                                       "KABlutdruck =  '" + patient.criticalBloodPressure + "', " +
                                       "NormPuls =  '" + patient.normalPulse + "', " +
                                       "ZAPuls =  '" + patient.acceptablePulse + "', " +
                                       "KAPuls =  '" + patient.criticalPulse + "', " +
                                       "NormGewicht =  '" + patient.normalWeight + "', " +
                                       "ZAGewicht =  '" + patient.acceptableWeight + "', " +
                                       "KAGewicht =  '" + patient.criticalWeight + "', " +
                                       "Status =  '" + Convert.ToInt32(patient.status) + "', " +
                                       "Uberprufung1 =  TIME('" + patient.checkTime1.ToShortTimeString() + "'), " +
                                       "Uberprufung2 =  TIME('" + patient.checkTime2.ToShortTimeString() + @"') 
                                       WHERE ID = " + patient.id + ";";

                command.ExecuteNonQuery();

                // Anything else indeed
                command.CommandText = "UPDATE steuerdaten SET KR = '" + Convert.ToInt32(patient.kr) + "', WHERE ID = " + patient.id + ";";
                command.ExecuteNonQuery();
           }

           return true;
       }


       /// <summary>
       /// Checks whether Patient exists
       /// </summary>
       /// <param name="name"> Last name of patient </param>
       /// <param name="firstName"> First name of patient </param>
       /// <returns> True if patient exists, false otherwise </returns>        
       public Boolean patientExists(String name, String firstName) 
       {
           Boolean ret = false;

           MySqlCommand command = connection.CreateCommand();
           command.CommandText = "SELECT m.ID "+
                                "FROM Mapping m, Patientendaten p "+
                                "WHERE p.Nachname = '"+name+"' "+
                                "AND p.Vorname = '"+firstName+"' "+
                                "AND m.ID = p.ID "+
                                "AND p.Status = 1;";

           MySqlDataReader Reader;
           Reader = command.ExecuteReader();

           if(Reader.HasRows)
             ret = true;

           Reader.Close();

           return ret;
       }

       /// <summary>
       /// Checks whether Patient exists
       /// </summary>
       /// <param name="patient"> Patient to check for </param>
       /// <returns> True if patient exists, false otherwise </returns>
       public Boolean patientExists(Patient patient)
       {
          return patientExists(patient.name, patient.firstName);
       }

       /// <summary>
       /// Gets patient by first and last name
       /// </summary>
       /// <param name="name"> Last name of patient </param>
       /// <param name="firstName"> First name of patient </param>
       /// <returns> Patient Logic object containing patient information </returns>
       public Patient getPatient(String name, String firstName) 
       {
           Patient newPatient = new Patient(name, firstName);

           MySqlCommand command = connection.CreateCommand();
           command.CommandText = @"SELECT m.ID, p.Geburtsdatum, p.Geschlecht, p.Diagnose, m.TEL, p.TelKontakt2, 
                                   p.NormBlutdruck, p. ZABlutdruck, p.KABlutdruck, 
                                   p.NormPuls, p.ZAPuls, p.KAPuls, p.NormGewicht, 
                                   p.ZAGewicht, p.KAGewicht, p.Status, p.Uberprufung1, p.Uberprufung2, s.KR
                                   FROM Mapping m, Patientendaten p, Steuerdaten s 
                                   WHERE 
                                   p.Nachname = '" + name + @"' AND 
                                   p.Vorname  = '" + firstName + @"' AND 
                                   m.ID = p.ID AND
                                   m.ID = s.ID;";

           MySqlDataReader reader = command.ExecuteReader();

           if(!reader.HasRows)
               throw new Exception("Patient does not exist");
           
           reader.Read();

           newPatient.id = reader.GetInt32("ID");
           newPatient.dateOfBirth = DateTime.Parse(reader.GetString("Geburtsdatum"));
           switch(reader.GetString("Geschlecht"))
           {
               case("f"):
                  newPatient.gender = Gender.Weiblich;
                  break;
               case("m"):
                  newPatient.gender = Gender.Männlich;
                  break;
               default:
                  newPatient.gender = Gender.Keins;
                  break;
           }
           newPatient.diagnosis = reader.GetString("Diagnose");
           newPatient.telephoneNumber = reader.GetString("TEL");
           newPatient.secondTelephoneNumber = reader.GetString("TelKontakt2");

/*         WTF... why was it even converted in the first place?!
           newPatient.normalBloodPressure     = reader.GetInt32("NormBlutdruck");
           newPatient.acceptableBloodPressure = ((float)reader.GetInt32("ZABlutdruck")/100f);
           newPatient.criticalBloodPressure   = ((float)reader.GetInt32("KABlutdruck")/100f);

           newPatient.normalPulse     = reader.GetInt32("NormPuls");
           newPatient.acceptablePulse = ((float)reader.GetInt32("ZAPuls")/100f);
           newPatient.criticalPulse   = ((float)reader.GetInt32("KAPuls")/100f);

           newPatient.normalWeight     = reader.GetInt32("NormGewicht");
           newPatient.acceptableWeight = ((float)reader.GetInt32("ZAGewicht")/100f);
           newPatient.criticalWeight   = ((float)reader.GetInt32("KAGewicht")/100f);
*/
           newPatient.normalBloodPressure     = reader.GetInt32("NormBlutdruck");
           newPatient.acceptableBloodPressure = reader.GetInt32("ZABlutdruck");
           newPatient.criticalBloodPressure   = reader.GetInt32("KABlutdruck");

           newPatient.normalPulse     = reader.GetInt32("NormPuls");
           newPatient.acceptablePulse = reader.GetInt32("ZAPuls");
           newPatient.criticalPulse   = reader.GetInt32("KAPuls");

           newPatient.normalWeight     = reader.GetInt32("NormGewicht");
           newPatient.acceptableWeight = reader.GetInt32("ZAGewicht");
           newPatient.criticalWeight   = reader.GetInt32("KAGewicht");

           newPatient.status = ((reader.GetInt32("Status") == 1) ? Status.Aktiv : Status.Inaktiv);

           newPatient.checkTime1 = DateTime.Parse(reader.GetString("Uberprufung1"));
           newPatient.checkTime2 = DateTime.Parse(reader.GetString("Uberprufung2"));

           newPatient.kr = Convert.ToBoolean(reader.GetUInt32("KR"));

           reader.Close();

           return newPatient;
       }


       /// <summary>
       /// method to get formated vitalData as lineChart 
       /// every series contains all points
       /// </summary>
       /// <param name="lastName"> Last name of patient </param>
       /// <param name="firstName"> First name of patient </param>
       /// <returns>([0]=BloodPressure, [1]=Pulse, [2]=Weight) of this person, null if mistake</returns>
       public Series[] getVitalDataChart(String lastName, String firstName)
       {
          VData[] data = this.getHistory(firstName, lastName);

          // Create headline
          Series[] pSeries = new Series[3];

          pSeries[0] = new Series("BlutDruck");
          pSeries[1] = new Series("Puls");
          pSeries[2] = new Series("Gewicht");

          // Add values
          for (int i = 0; i < data.Length; i++)
          {
             pSeries[0].Points.AddXY(data[i].time, data[i].bloodpressure);
             pSeries[1].Points.AddXY(data[i].time, data[i].pulse);
             pSeries[2].Points.AddXY(data[i].time, data[i].weight);
          }

          return pSeries;
       }

       /// <summary>
       /// method to get formated vitalData as lineChart 
       /// every series contains all points
       /// </summary>
       /// <param name="patient"> Patient to get data for </param>
       /// <returns>([0]=BloodPressure, [1]=Pulse, [2]=Weight) of this person, null if mistake</returns>
       public Series[] getVitalDataChart(Patient patient)
       {
          return getVitalDataChart(patient.name, patient.firstName);
       }


       /// <summary>
       /// method to get formated vitalData as gridChart
       /// </summary>
       /// <param name="firstName"> first name of patient </param>
       /// <param name="lastName"> last name of patient </param>
       /// <returns>n objects[](lines) with m strings(columns)(first object is headline, youngest date first), null if mistake</returns>
       public object[] getVitalDataGrid(String lastName, String firstName)
       {
          List<object> rows = new List<object>();

          // Create headline
          rows.Add(new String[] { "Zeit", "Puls", "Blutdruck", "Gewicht" });

          // Get rows
          VData[] data = this.getHistory(firstName, lastName);

          // Add rows
          for (int i = 0; i < data.Length; i++)
             rows.Add(new string[] { data[i].time.ToString(), data[i].pulse.ToString(), data[i].bloodpressure.ToString(), data[i].weight.ToString() });

          return rows.ToArray();
       }

       /// <summary>
       /// Method to get formated vitalData as gridChart
       /// </summary>
       /// <param name="patient"> Patient to get data for </param>
       /// <returns>n objects[](lines) with m strings(columns)(first object is headline, youngest date first), null if mistake</returns>
       public object[] getVitalDataGrid(Patient patient)
       {
          return getVitalDataGrid(patient.name, patient.firstName);
       }
   }
}
